<?php

namespace app\library;

use PHPExcel;
use think\facade\Env;

/**
 * Excel 导出工具
 * Class Excel
 * @package app\library
 */
Class Excel extends PHPExcel {

    /**
     * 索引
     * @var int 
     */
    public $index = 0;

    /**
     * constructor
     */
    public function __construct() {
        parent::__construct();
    }

    /**
     * 设置sheet
     * @param string $title 标题
     * @return $this
     */
    public function setSheet($title = '') {
        $this->setActiveSheetIndex($this->index);
        $title && $this->getActiveSheet()->setTitle($title);
        return $this;
    }

    /**
     * 单元格索引
     * @param $index 
     * @param int $start 
     * @return string
     */
    public function getCellIndex($index, $start = 65) {
        return chr(intval($index) + $start - 1);
    }

    /**
     * 渲染数据
     * @param type $header 头部
     * @param type $data 内容
     * @param type $option 配置
     * @return $this
     */
    public function renderData($header = [], $data = [], $option = []) {
        $sheet = $this->getSheet($this->index);
        // 水平居中
        if (isset($option['horizontal_center'])) {
            $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        }
        $row = $column = 1;
        // 写入头部
        foreach ($header as $key => $value) {
            $sheet->setCellValue($this->getCellIndex($column) . $row, $value);
            $column++;
        }
        // 写入内容
        $row = 2;
        $header_key = array_keys($header);
        foreach ($data as $key => $value) {
            $column = 1;
            foreach ($header_key as $k => $v) {
                $cell = '';
                if (strpos($v, '|') !== false) {
                    list($field, $method) = explode('|', $v);
                    if (isset($value[$field])) {
                        if (strpos($method, ',') !== false) {
                            list($method, $param) = explode(',', $method);
                            $cell = $method($value[$field], $param);
                        } else {
                            $cell = $method($value[$field]);
                        }
                    }
                } else {
                    isset($value[$v]) && $cell = $value[$v];
                }
                // 单元格格式
                if (isset($option['data_type'])) {
                    $sheet->setCellValueExplicit($this->getCellIndex($column) . $row, $cell);
                } else {
                    $sheet->setCellValue($this->getCellIndex($column) . $row, $cell);
                }
                $column++;
            }
            $row++;
        }
        return $this;
    }

    /**
     * 下载
     * @param string $fileName 文件名
     * @param string $type 类型
     */
    public function download($fileName = '', $type = '2007') {
        // 默认文件名
        if ($fileName === '') {
            $fileName = time();
        }
        // excel类型
        if ($type === '2003') {
            $type = 'Excel5';
            header('Content-Type: application/vnd.ms-excel');
            $extension = '.xls';
        } else {
            $type = 'Excel2007';
            // Redirect output to a client’s web browser (Excel2007)
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            $extension = '.xlsx';
        }
        // 设置文件名
        $fileName = iconv('utf-8', 'gb2312', $fileName);
        header('Content-Disposition: attachment;filename="' . $fileName . $extension . '"');
        // 网页缓存
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        // 创建excel文件
        $objWriter = \PHPExcel_IOFactory::createWriter($this, $type);
        $objWriter->save('php://output');
        exit;
    }

    /**
     * 上传
     * @param string|array $fileName 文件名
     * @param type $path 文件路径
     * @param type $field 表字段
     * @param type $table 表名
     * @return int
     */
    public function upload($fileName = '', $path = '', $field = '', $table = '') {
        set_time_limit(0);
        ini_set('memory_limit', '512M');
        !$path && $path = Env::get('root_path') . '/public'; // 默认文件路径
        if (is_array($fileName)) { // 是否文件流
            list($name, $tmp_name) = [$fileName['name'], $fileName['tmp_name']];
            $file_name = date('YmdHis') . '_' . getRandomString('numeric') . '_' . $name;
            $file = $path . '/' . $file_name;
            move_uploaded_file($tmp_name, $file);
        } else {
            $file = $path . '/' . $fileName;
        }
        // 读取文件
        $PHPExcel = \PHPExcel_IOFactory::load($file);
        $sheet = $PHPExcel->getSheet(0); // 第一个工作表开始
        $activeSheet = $PHPExcel->getActiveSheet(); // 单元格
        $highestRow = $sheet->getHighestRow(); // 行数
        $data = $field_arr = [];
        if ($field) { // 是否自定义字段
            $field_arr = !is_array($field) ? explode(',', $field) : $field;
        } else {
            $highestColumn = $sheet->getHighestColumn(); // 最大列
            $column_index = $this->getColumnIndex($highestColumn);
            foreach ($column_index as $key => $value) { // 渲染字段
                $field_arr[] = $activeSheet->getCell($this->getCellIndex($key + 1) . 1)->getFormattedValue() ?: '';
            }
        }
        for ($i = 2; $i <= $highestRow; $i++) { // 第二行开始
            // 渲染列数据
            foreach ($field_arr as $key => $value) {
                $data[$i][$value] = $activeSheet->getCell($this->getCellIndex($key + 1) . $i)->getFormattedValue() ?: '';
            }
        }
        // 批量添加数据
        $table && \think\Db::name($table)->insertAll($data);
        return $data;
    }

    /**
     * 列索引
     * @param type $column 列
     * @return array
     */
    public function getColumnIndex($column) {
        $column_key = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];
        $column_index = [];
        foreach ($column_key as $value) {
            $column_index[] = $value;
            if ($value == $column) {
                break;
            }
        }
        return $column_index;
    }

}
